﻿using System;
using System.Collections.Generic;
using System.Text;
using BarCode.Bean;
using BarCode.Common;
using System.Data;
using BarCode.Module;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace BarCode.AccessDao
{
    public class DB02Dao
    {
        private static readonly log4net.ILog LogUtil = log4net.LogManager.GetLogger(typeof(DB02Dao));

        private MySqlHelper db02;
        public DB02Dao()
        {
            db02 = new MySqlHelper(GlobalVal.MysqlServerInfo.SERVER_IP, "db_02", GlobalVal.MysqlServerInfo.USERID, GlobalVal.MysqlServerInfo.USERPWD);
        }

        /// <summary>
        /// 判断是否可链接
        /// </summary>
        /// <returns></returns>
        public bool EnableConnect()
        {
            return db02.EnableConnect();
        }



        /// <summary>
        /// 获取表号范围内所有合格的表号
        /// </summary>
        /// <returns></returns>
        public Dictionary<String, String> getAllQualifiedBH(string startCode, string endCode, ref Dictionary<String,String> unQualifiedDic)
        {

            Dictionary<String, String> retDic = new Dictionary<String, String>();
            Dictionary<string, string> bhDic = new Dictionary<string, string>();
            unQualifiedDic = new Dictionary<String, String>();

            try
            {
                //string selectSql = "select SerialNumber from Meters where MeterAccepted IN (1,-1) and SerialNumber >= @startCode and SerialNumber <= @endCode  ";
                string selectSql = "";
                selectSql += " SELECT M.SerialNumber SerialNumber, M.MeterAccepted MeterAccepted ";
                selectSql += " FROM     ";
                selectSql += "    (select CalibrationId,EquipmentId ,SerialNumber,MeterAccepted from Meters where SerialNumber >= upper(@startCode) and SerialNumber <= upper(@endCode)    ";
                selectSql += "         union select CalibrationId,EquipmentId ,SerialNumber,MeterAccepted from Meters where SerialNumber >= lower(@startCode) and SerialNumber <= lower(@endCode) ) M   ";
                selectSql += "    INNER JOIN Calibrations C ON M.CalibrationId = C.CalibrationId AND M.EquipmentId = C.EquipmentId     ";
                selectSql += " ORDER BY M.SerialNumber, C.Date DESC     ";

                MySqlParameter[] sqlParams = new MySqlParameter[2];
                sqlParams[0] = new MySqlParameter("@startCode", startCode);
                sqlParams[1] = new MySqlParameter("@endCode", endCode);
                LogUtil.Debug("获取表号范围内所有合格的表号的查询SQL：" + selectSql);
                DataSet retSet = db02.GetDataSet(CommandType.Text, selectSql, sqlParams);

                if (retSet != null && retSet.Tables.Count > 0)
                {
                    for (int i = 0; i < retSet.Tables[0].Rows.Count; i++)
                    {
                        DataRow tmpDR = retSet.Tables[0].Rows[i];
                        string tmpBH = Convert.ToString(tmpDR["SerialNumber"]);
                        if (!bhDic.ContainsKey(tmpBH))
                        {
                            if (DBNull.Value != tmpDR["MeterAccepted"] &&
                                ("1".Equals(tmpDR["MeterAccepted"].ToString()) || "-1".Equals(tmpDR["MeterAccepted"].ToString())))
                            {
                                retDic.Add(tmpBH, tmpBH);
                            }
                            else
                            {
                                if (!unQualifiedDic.ContainsKey(tmpBH))
                                {
                                    // 19/05/21 单独追加不合格的表号
                                    unQualifiedDic.Add(tmpBH, tmpBH);
                                }                               
                            }
                            bhDic.Add(tmpBH, tmpBH);
                        }

                    }
                }
                return retDic;
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                return null;
            }
            
        }


        /// <summary>
        /// 获取表号范围内所有合格的表号
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> getAllQualifiedBH(string startCode, string endCode, double meters_tolmax, double meters_tolmin, ref Dictionary<string, string> unQualifiedDic)
        {

            Dictionary<string, string> bhDic = new Dictionary<string, string>(); // 保证只取最新的记录
            unQualifiedDic = new Dictionary<string, string>();

            try
            {
                string selectSql = "";
                selectSql += " SELECT M.SerialNumber SerialNumber, M.MeterAccepted MeterAccepted, C.CalibrationId ";
                selectSql += " FROM     ";
                selectSql += "    (select CalibrationId,EquipmentId ,SerialNumber,MeterAccepted from Meters where SerialNumber >= upper(@startCode) and SerialNumber <= upper(@endCode)    ";
                selectSql += "         union select CalibrationId,EquipmentId ,SerialNumber,MeterAccepted from Meters where SerialNumber >= lower(@startCode) and SerialNumber <= lower(@endCode) ) M   ";
                selectSql += "    INNER JOIN Calibrations C ON M.CalibrationId = C.CalibrationId AND M.EquipmentId = C.EquipmentId     ";
                selectSql += " ORDER BY M.SerialNumber, C.Date DESC     ";

                MySqlParameter[] sqlParams = new MySqlParameter[2];
                sqlParams[0] = new MySqlParameter("@startCode", startCode);
                sqlParams[1] = new MySqlParameter("@endCode", endCode);
                LogUtil.Debug("获取表号范围内所有合格的表号的查询SQL：" + selectSql);
                DataSet retSet = db02.GetDataSet(CommandType.Text, selectSql, sqlParams);

                if (retSet != null && retSet.Tables.Count > 0)
                {
                    for (int i = 0; i < retSet.Tables[0].Rows.Count; i++)
                    {
                        DataRow tmpDR = retSet.Tables[0].Rows[i];
                        string tmpBH = Convert.ToString(tmpDR["SerialNumber"]);
                        if (!bhDic.ContainsKey(tmpBH))
                        {
                            //if (DBNull.Value != tmpDR["MeterAccepted"] &&
                            //    ("1".Equals(tmpDR["MeterAccepted"].ToString()) || "-1".Equals(tmpDR["MeterAccepted"].ToString())))
                            //{
                            //    retDic.Add(tmpBH, tmpBH + "-" + tmpDR["CalibrationId"].ToString());
                            //}
                            bhDic.Add(tmpBH, tmpBH + "-" + tmpDR["CalibrationId"].ToString());
                        }

                    }
                }

                // 获取不合格的表号
                string getUnQualifiedSql = "";
                getUnQualifiedSql += " SELECT M.CalibrationId, M.EquipmentId, M.Position, M.SerialNumber, C.Date CreateDate ";
                getUnQualifiedSql += " FROM ";
                getUnQualifiedSql += " (SELECT CalibrationId, EquipmentId, Position,  SerialNumber FROM Meters WHERE SerialNumber >= UPPER(@startCode) AND SerialNumber <= UPPER(@endCode) ";
                getUnQualifiedSql += " UNION ";
                getUnQualifiedSql += " SELECT CalibrationId, EquipmentId, Position,  SerialNumber FROM Meters WHERE SerialNumber >= LOWER(@startCode) AND SerialNumber <= LOWER(@endCode) ";
                getUnQualifiedSql += " ) M ";
                getUnQualifiedSql += " INNER JOIN ";
                getUnQualifiedSql += " Calibrations C ON M.CalibrationId = C.CalibrationId AND M.EquipmentId = C.EquipmentId ";
                getUnQualifiedSql += " where ";
                getUnQualifiedSql += " exists ( ";
                getUnQualifiedSql += " select 1 from TestsReps tr where tr.CalibrationId = M.CalibrationId AND tr.EquipmentId = M.EquipmentId AND tr.Position = M.Position ";
                getUnQualifiedSql += " AND (tr.MeterError < @meters_tolmin or tr.MeterError > @meters_tolmax) AND tr.PointNum < 100 AND RealFlowRate >= 0.15";
                getUnQualifiedSql += " ) ";
                getUnQualifiedSql += " order by SerialNumber, C.Date desc ";
                MySqlParameter[] getUnQualifiedParams = new MySqlParameter[4];
                getUnQualifiedParams[0] = new MySqlParameter("@startCode", startCode);
                getUnQualifiedParams[1] = new MySqlParameter("@endCode", endCode);
                getUnQualifiedParams[2] = new MySqlParameter("@meters_tolmin", meters_tolmin);
                getUnQualifiedParams[3] = new MySqlParameter("@meters_tolmax", meters_tolmax);
                LogUtil.Debug("获取表号范围内所有不合格的表号的查询SQL：" + getUnQualifiedSql);

                DataSet unQualifiedSet = db02.GetDataSet(CommandType.Text, getUnQualifiedSql, getUnQualifiedParams);
                string updSql = "update Meters set MeterAccepted = 0 where CalibrationId=@CalibrationId and EquipmentId=@EquipmentId and Position=@Position ";
                if (unQualifiedSet != null && unQualifiedSet.Tables.Count > 0)
                {
                    for (int i = 0; i < unQualifiedSet.Tables[0].Rows.Count; i++)
                    {
                        DataRow tmpDR = unQualifiedSet.Tables[0].Rows[i];
                        string tmpBH = Convert.ToString(tmpDR["SerialNumber"]);
                        string tmpEqualStr = tmpBH + "-" + tmpDR["CalibrationId"].ToString();
                        if (bhDic.ContainsKey(tmpBH) && bhDic[tmpBH].Equals(tmpEqualStr))
                        {
                            if (!unQualifiedDic.ContainsKey(tmpBH))
                            {
                                unQualifiedDic.Add(tmpBH, tmpEqualStr);
                            }                            

                            // 更新Meters表记录
                            MySqlParameter[] updParams = new MySqlParameter[3];
                            updParams[0] = new MySqlParameter("@CalibrationId", tmpDR["CalibrationId"].ToString());
                            updParams[1] = new MySqlParameter("@EquipmentId", tmpDR["EquipmentId"].ToString());
                            updParams[2] = new MySqlParameter("@Position", tmpDR["Position"].ToString());
                            db02.ExecuteNonQuery(CommandType.Text, updSql, updParams);

                        }

                    }
                }

                return bhDic;
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                return null;
            }
        }

    }
}

